﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using BiffenService.Model;

namespace BiffenService.Db
{

    public class MemberDb : IMemberDb
    {
        private static SqlCommand command = null;

        /// <summary>
        /// Method that inserts a member into the database
        /// </summary>
        /// <param name="name">String name the name of the new member</param>
        /// <param name="phone">int phone the phone number of the new member</param>
        /// <param name="email">String email the email address of the new memeber</param>
        /// <param name="Address">String address the address of the new member</param>
        /// <param name="zipcode">int zipcode the zipcode the new member lifes in</param>
        /// <param name="newsletter">bool newsletter true if the new member wants to recieve a newsletter, false if not</param>
        /// <returns>int result, the number of rows affected in the sql database</returns>
        public int InsertMemberIntoDatabase(string name, int phone, string email, string Address, int zipcode, bool newsletter)
        {
            throw new NotImplementedException();
            int result = -1;
            try
            {
                string sql = @"INSERT INTO bif_member (Name, Phone, Email, Address, Zipcode, Newsletter) 
                                VALUES (@Name, @Phone, @Email, @Address, @Zipcode, @Newsletter)";

                command = DbClient.GetDbCommand(sql);
                command.Parameters.AddWithValue("@Name", name);
                command.Parameters.AddWithValue("@Phone", phone);
                command.Parameters.AddWithValue("@Email", email);
                command.Parameters.AddWithValue("@Address", Address);
                command.Parameters.AddWithValue("@Zipcode", zipcode);
                command.Parameters.AddWithValue("@Newsletter", newsletter);

                result = command.ExecuteNonQuery();

                DbClient.Close();
            }

            catch (SqlException sqlE)
            {
                Console.WriteLine("Sql error goddammit! int createMember: " + sqlE.Message);
                return -1;
            }
            catch (Exception e)
            {
                Console.WriteLine("Ordinary Error in createMember: " + e.Message);
                return -1;
            }

            return result;
        }

        /// <summary>
        /// Method to find a member from the database with a specific id
        /// </summary>
        /// <param name="memberId">int memberId the id of the member we want to find</param>
        /// <returns>Member member the member found in the database</returns>
        public Member FindMemberByIdFromDatabase(int memberId)
        {
            Member member = new Member();
            try
            {
                string sql = @"select * from bif_member where Id = " + memberId;

                command = DbClient.GetDbCommand(sql);
                IDataReader dbReader = command.ExecuteReader();
                if (dbReader.Read())
                {
                    member.Id = (int)(dbReader["Id"]);
                    member.Name = (String)(dbReader["Name"]);
                    member.Phone = (int)(dbReader["Phone"]);
                    member.Email = (String)(dbReader["Email"]);
                    member.Address = (String)(dbReader["Address"]);
                    member.Zipcode = (int)(dbReader["Zipcode"]);
                    member.Newsletter = (Boolean)(dbReader["Newsletter"]);
                }
                dbReader.Close();
                DbClient.Close();
            }
            catch (SqlException SqlE)
            {
                Console.WriteLine("SQL Exception in FindMemberByIdFromDatabase " + SqlE.Message);
            }
            catch (Exception E)
            {
                Console.WriteLine("Exception in FindMemberByIdFromDatabase " + E.Message);
            }
            return member;
        }

        /// <summary>
        /// Method to find a members id from the database with the members phone number
        /// </summary>
        /// <param name="phone">int phone the member we want to find phone number</param>
        /// <returns>int memberId the id of the member found in the database</returns>
        public int FindMemberIdByPhone(int phone)
        {
            int memberId = -1;
            try
            {
                string sql = @"select Id from bif_member where Phone = " + phone;

                command = DbClient.GetDbCommand(sql);
                IDataReader dbReader = command.ExecuteReader();
                if (dbReader.Read())
                {
                    memberId = (int)(dbReader["Id"]);
                }
                dbReader.Close();
                DbClient.Close();
            }
            catch (SqlException SqlE)
            {
                Console.WriteLine("SQL Exception in FindMemberIdByPhone " + SqlE.Message);
            }
            catch (Exception E)
            {
                Console.WriteLine("Exception in FindMemberIdByPhone " + E.Message);
            }
            return memberId;
        }

        /// <summary>
        /// Method that finds a Member by a phone number
        /// </summary>
        /// <param name="phone">int phone the members phone number</param>
        /// <returns>Member member the member found in the database</returns>
        public Member FindMemberByPhone(int phone)
        {
            int memberId = FindMemberIdByPhone(phone);
            Member member = FindMemberByIdFromDatabase(memberId);

            return member;
        }


        #region . Login .
        public Login GetLoginByPhone(int phone)
        {
            string sql = "SELECT * FROM bif_member M, bif_login L WHERE M.Id=L.MemberId AND M.Phone=@Phone";
            SqlCommand cmd = DbClient.GetDbCommand(sql);
            cmd.Parameters.AddWithValue("@Phone", phone);

            SqlDataReader r = cmd.ExecuteReader();
            Login l = null;
            while (r.Read())
            {
                // Build member
                Member m = new Member();
                m.Id = (int)r["Id"];
                m.Name = (string)r["Name"];
                m.Phone = (int)r["Phone"];
                m.Email = (string)r["Email"];
                m.Address = (string)r["Address"];
                m.Zipcode = (int)r["Zipcode"];
                m.Newsletter = (bool)r["Newsletter"];

                // Build login
                l = new Login();
                l.Member = m;
                l.Password = (string)r["Password"];
                l.Rank = (int)r["Rank"];
            }
            r.Close();
            DbClient.Close();

            return l;
        }

        #endregion
    }
}
